package com.gack.business.dao;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.gack.business.fkbModel.output.DepartmentOutputData;
import com.gack.business.fkbModel.output.EnterpriseOutputData;
import com.gack.business.fkbModel.output.PeopleOutputData;
import com.gack.business.fkbModel.output.PositionOutputData;

import io.jsonwebtoken.lang.Collections;

/**
 * 
 * @author ws
 * 2018-7-18
 */
@Repository
public class FKBDao {

	@Autowired
	private EntityManager em;
	
	public List<EnterpriseOutputData> findAllEnterprise(){
		String jpql = "select id as orgcode,"
				+ " name as codename,"
				+ " case status"
				+ " when '4' then delete_time"
				+ " else update_name_time"
				+ " end as ts,"
				+ " case status"
				+ " when '4' then 1"
				+ " else 0"
				+ " end as dr"
				+ " from enterprise";
		
		Query query = em.createNativeQuery(jpql, EnterpriseOutputData.class);
		
		return query.getResultList();
	}
	
	public List<EnterpriseOutputData> findEnterpriseById(String id){
		String jpql = "select id as orgcode,"
				+ " name as codename,"
				+ " case status"
				+ " when '4' then delete_time"
				+ " else update_name_time"
				+ " end as ts,"
				+ " case status"
				+ " when '4' then 1"
				+ " else 0"
				+ " end as dr"
				+ " from enterprise"
				+ " where id=:id";
		
		Query query = em.createNativeQuery(jpql, EnterpriseOutputData.class);
		query.setParameter("id", id);
		
		return query.getResultList();
	}
	
	public List<DepartmentOutputData> findDepartmentByEnterpriseId(String enterpriseId){
		String jpql = "select enterprise_id as orgcode,"
				+ " id as deptcode,"
				+ " name as deptname,"
				+ " parent_id as fatherdept,"
				+ " case status"
				+ " when 0 then delete_time"
				+ " else update_name_time"
				+ " end as ts,"
				+ " case status"
				+ " when 0 then 1"
				+ " else 0"
				+ " end as dr"
				+ " from department"
				+ " where enterprise_id=:enterpriseId";
		
		Query query = em.createNativeQuery(jpql, DepartmentOutputData.class);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	public List<DepartmentOutputData> findDepartmentByDepartmentIds(List<String> departmentIds){
		if(Collections.isEmpty(departmentIds)){
			return new ArrayList<DepartmentOutputData>();
		}
		
		String jpql = "select enterprise_id as orgcode,"
				+ " id as deptcode,"
				+ " name as deptname,"
				+ " parent_id as fatherdept,"
				+ " case status"
				+ " when 0 then delete_time"
				+ " else update_name_time"
				+ " end as ts,"
				+ " case status"
				+ " when 0 then 1"
				+ " else 0"
				+ " end as dr"
				+ " from department"
				+ " where id in";
		
		StringBuilder sb = new StringBuilder("(");
		for(String temp : departmentIds){
			sb.append(" '" + temp + "',");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-1));
		sb.append(")");
		jpql += sb.toString();
		
		Query query = em.createNativeQuery(jpql, DepartmentOutputData.class);
		
		return query.getResultList();
	}
	
	public List<PositionOutputData> findPositionByEnterpriseId(String enterpriseId){
		String jpql = "select new com.gack.business.fkbModel.output.PositionOutputData("
				+ "	p.jobrankId,"
				+ " (select name from JobRank where id=p.jobrankId),"
				+ " (select level from JobRank where id=p.jobrankId),"
				+ "	p.id,"
				+ "	p.name,"
				+ "	p.enterpriseId,"
				+ " (select sort from JobRankSort where enterpriseId=:enterpriseId),"
				+ "	(select case status when 0 then deleteTime when 1 then updateTime end from Position where id=p.id),"
				+ " (select case status when 0 then 1 when 1 then 0 end  from Position where id=p.id)"
				+ "	)"
				+ " from Position p"
				+ " where p.enterpriseId=:enterpriseId";
		
		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	public List<PeopleOutputData> findPeopleByEnterpriseId(String enterpriseId){
		String jpql = "select new com.gack.business.fkbModel.output.PeopleOutputData("
				+ " u.id,"
				+ " u.nickname,"
				+ " u.username,"
				+ " u.update_name_time,"
				+ " (select enterpriseId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select positionId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select departmentId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select enterpriseRemarks from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select updateTime from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id)"
				+ ")"
				+ " from User u"
				+ " where u.id in (select uedp.userId"
								+ " from UserEnterpriseDepartmentPosition uedp"
								+ " where uedp.enterpriseId=:enterpriseId)";

		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	public List<PeopleOutputData> findPeopleByEnterpriseIdAndDepartmentId(String enterpriseId, List<String> departmentIds){
		if(Collections.isEmpty(departmentIds)){
			return new ArrayList<PeopleOutputData>();
		}
		
		String jpql = "select new com.gack.business.fkbModel.output.PeopleOutputData("
				+ " u.id,"
				+ " u.nickname,"
				+ " u.username,"
				+ " u.update_name_time,"
				+ " (select enterpriseId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select positionId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select departmentId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select enterpriseRemarks from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select updateTime from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id)"
				+ ")"
				+ " from User u"
				+ " where u.id in (select uedp.userId"
								+ " from UserEnterpriseDepartmentPosition uedp"
								+ " where uedp.enterpriseId=:enterpriseId"
								+ " and uedp.departmentId in ";

		StringBuilder sb = new StringBuilder("(");
		for(String temp : departmentIds){
			sb.append(" '" + temp + "',");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-1));
		sb.append("))");
		jpql += sb.toString();
		
		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	public List<PeopleOutputData> findPeopleByEnterpriseIdAndUserId(String enterpriseId, String userId){
		String jpql = "select new com.gack.business.fkbModel.output.PeopleOutputData("
				+ " u.id,"
				+ " u.nickname,"
				+ " u.username,"
				+ " u.update_name_time,"
				+ " (select enterpriseId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select positionId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select departmentId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select enterpriseRemarks from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select updateTime from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id)"
				+ ")"
				+ " from User u"
				+ " where u.id=:userId and u.id in (select userId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId) ";

		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		
		return query.getResultList();
	}
	
	public List<PeopleOutputData> findPeopleByEnterpriseIdAndUserId(String enterpriseId, List<String> departmentIds, String userId){
		if(Collections.isEmpty(departmentIds)){
			return new ArrayList<PeopleOutputData>();
		}
		
		String jpql = "select new com.gack.business.fkbModel.output.PeopleOutputData("
				+ " u.id,"
				+ " u.nickname,"
				+ " u.username,"
				+ " u.update_name_time,"
				+ " (select enterpriseId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select positionId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select departmentId from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select enterpriseRemarks from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id),"
				+ " (select updateTime from UserEnterpriseDepartmentPosition where enterpriseId=:enterpriseId and userId=u.id)"
				+ ")"
				+ " from User u"
				+ " where u.id=:userId and u.id in (select uedp.userId"
													+ " from UserEnterpriseDepartmentPosition uedp"
													+ " where uedp.enterpriseId=:enterpriseId"
													+ " and uedp.departmentId in ";

		StringBuilder sb = new StringBuilder("(");
		for(String temp : departmentIds){
			sb.append(" '" + temp + "',");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-1));
		sb.append("))");
		jpql += sb.toString();
		
		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		
		return query.getResultList();
	}
	
}
